﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using DAO;
using DataAccess;
namespace BUL
{
    public class StaffBUL
    {
        ConnectDatabase cd = new ConnectDatabase();
        public DataTable load()
        {
            String sql = "select idStaff as 'Mã NV', fullname as 'Họ tên', birthday as 'Ngày sinh', address as 'Địa chỉ', position as 'Chức vụ', gender as 'Giới tính', phone as 'ĐT', salary as 'Lương CB' from staff";
            return cd.ExcuteQuery(sql);
        }
        public Boolean insert(StaffDAO p)
        {
            String sql = @"insert into staff (fullname, birthday, address, position, gender, phone, salary, idQualification, idExperience) values(@name,@birth,@address,@position,@gender,@phone,@salary,@idQua,@idEx)";
            int count = 9;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@name";
            name[1] = "@birth";
            name[2] = "@address";
            name[3] = "@position";
            name[4] = "@gender";
            name[5] = "@phone";
            name[6] = "@salary";
            name[7] = "@idQua";
            name[8] = "@idEx";
            value[0] = p.Fullname;
            value[1] = p.Birthday;
            value[2] = p.Address;
            value[3] = p.Position;
            value[4] = p.Gender;
            value[5] = p.Phone;
            value[6] = p.Salary;
            value[7] = p.IdQualification;
            value[8] = p.IdExperience;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean update(StaffDAO p)
        {
            String sql = @"update staff set fullname=@name, birthday=@birth, address=@address, position=@position, gender=@gender, phone=@phone, salary=@salary where idStaff=@id";
            int count = 8;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@name";
            name[1] = "@birth";
            name[2] = "@address";
            name[3] = "@position";
            name[4] = "@gender";
            name[5] = "@phone";
            name[6] = "@salary";
            name[7] = "@id";
            value[0] = p.Fullname;
            value[1] = p.Birthday;
            value[2] = p.Address;
            value[3] = p.Position;
            value[4] = p.Gender;
            value[5] = p.Phone;
            value[6] = p.Salary;
            value[7] = p.IdStaff;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean delete(String id)
        {
            String sql = @"delete from staff where idStaff = '" + id + "'";
            return cd.ExcuteNonQuery(sql);
        }
        public String getFullName(String id) {
            String sql = "select fullname from staff where idStaff= '" + id + "'";
            return cd.ExcuteQuery(sql).Rows[0][0].ToString();
        }
        public String getPositionById(String id)
        {
            String sql = "select position from staff where idStaff= '" + id + "'";
            return cd.ExcuteQuery(sql).Rows[0][0].ToString();
        }
        public DataTable loadId() {
            String sql = "select idStaff from staff ";
            return cd.ExcuteQuery(sql);
        }
    }
}
